package com.oreilly.ez.web;


import com.alibaba.fastjson.JSON;
import com.oreilly.ez.entity.SysUser;
import com.oreilly.ez.util.ExcelUtil;
import com.oreilly.ez.vo.UserInfo;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@RestController
@RequestMapping("/sys")
public class SysCtrl {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/exportDB")
    public void exportDB(HttpServletResponse response) throws Exception {


        List<String> tableNameList = jdbcTemplate.queryForList("SELECT name  FROM sqlite_master WHERE type = 'table'", String.class);
        response.setContentType("APPLICATION/OCTET-STREAM");
        response.setHeader("Content-Disposition", "attachment; filename=db.zip");
        ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream());
//        System.out.println(tableNameList);
        if (tableNameList != null && tableNameList.size() > 0) {

            for (String tname : tableNameList) {
                List<Map<String, Object>> queryList = jdbcTemplate.queryForList("select * from '" + tname + "'");


                // add one entry
                ZipEntry zipEntry = new ZipEntry("db/" + tname + ".json");
                zipOut.putNextEntry(zipEntry);

                // write file stream to entry
                String text = JSON.toJSONString(queryList);
                byte[] bytes = text.getBytes("UTF-8");

                zipOut.write(bytes);

                zipOut.flush();

                zipOut.closeEntry();
            }
        }

        zipOut.close();

    }


    @GetMapping("/export-excel")
    public void exportExcel(HttpServletResponse response) throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();


        //
//        DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
////文档类别
//        dsi.setCategory("员工信息");
////设置文档管理员
//        dsi.setManager("江南一点雨");
////设置组织机构
//        dsi.setCompany("XXX集团");
////获取摘要信息并配置
//        SummaryInformation si = workbook.getSummaryInformation();
////设置文档主题
//        si.setSubject("员工信息表");
////设置文档标题
//        si.setTitle("员工信息");
////设置文档作者
//        si.setAuthor("XXX集团");
////设置文档备注
//        si.setComments("备注信息暂无");


        HSSFSheet sheet = workbook.createSheet("员工信息表");
        HSSFRow headerRow = sheet.createRow(0);
        HSSFCell cell0 = headerRow.createCell(0);
        cell0.setCellValue("编号");

        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=student.xls");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();


    }
    @GetMapping("/export-excel-annotation")
    public void exportExcelAnnotation(HttpServletResponse response) throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();


        List<UserInfo> userInfoList = new ArrayList<>();

        UserInfo userInfo1 = new UserInfo();
        userInfo1.setId(1000);
        userInfo1.setName("许周");
        userInfo1.setGender(1);

        userInfoList.add(userInfo1);

        ExcelUtil.createSheet(workbook, "员工信息表", userInfoList);

        UserInfo userInfo2 = new UserInfo();
        userInfo2.setId(2000);
        userInfo2.setName("王ss");
        userInfo2.setGender(2);
        userInfo2.setBirth(DateTime.parse("1990-08-15").toDate());

        userInfoList.add(userInfo2);
        ExcelUtil.createSheet(workbook, "员工信息表222", userInfoList);


//        HSSFSheet sheet = workbook.createSheet("员工信息表");
//        HSSFRow headerRow = sheet.createRow(0);
//        HSSFCell cell0 = headerRow.createCell(0);
//        cell0.setCellValue("编号");

        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=annotation.xls");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();


    }




}
